package com.decent.ejfadmin.collectcard.dao.provider;

import com.decent.ejfadmin.collectcard.entity.SupplyCardViewBean;
import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.common.bean.SupplyCard;
import com.decent.ejfadmin.utils.SQLWrapUtils;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * @author weicx
 * @date 2018/07/17
 */
@Slf4j
public class CollectCardRecordDaoProvider {
    private static final Map<String, String> CONDITION;

    static {
        HashMap<String, String> hashMap = new HashMap<>();
        hashMap.put("0", "  t.dealingCount > 0");
        hashMap.put("3", "  t.preCount > 0 ");
        hashMap.put("4", "  t.preJudge > 0");
        hashMap.put("5", "  t.wrongFace > 0");
        hashMap.put("2", "  t.failCount > 0");
        hashMap.put("1", "  t.successCount > 0");
        hashMap.put("7", "  t.waitSlowTrySell > 0");
        hashMap.put("9", "  t.waitSettlement > 0");
        hashMap.put("10", "  t.disputing > 0");
        hashMap.put("99", "  t.fixCount > 0");
        CONDITION = Collections.unmodifiableMap(hashMap);
    }

    /**
     * 收卡记录列表计数
     *
     * @param params 参数
     * @return sql
     */
    public String apiQuerySupplyOrderListCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select count(1) from ( ");
        generateSupplyCardApiQuerySql(sql, params);
        sql.append(" ) t,\n" +
                "yuhuang_sup_user u,yuhuang_sup_info i\n" +
                "WHERE t.userId=u.user_id\n" +
                "AND u.user_id=i.user_id    ");
        generateSupplyCardApiQueryStatusSql(params, sql);
        log.info("apiQuerySupplyOrderListCount={}",sql);
        return sql.toString();
    }

    /**
     * 分页查询销卡订单(批次分组)
     *
     * @param params 参数
     * @return sql
     */
    public String apiQuerySupplyOrderListPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select t.*,u.account userAccount,i.`name` userName from ( ");
        generateSupplyCardApiQuerySql(sql, params);
        sql.append(" ) t,\n" +
                "yuhuang_sup_user u,yuhuang_sup_info i\n" +
                "WHERE t.userId=u.user_id\n" +
                "AND u.user_id=i.user_id   ");
        generateSupplyCardApiQueryStatusSql(params, sql);
        sql.append(" ORDER BY\n" +
                "\t\t\tstartDate DESC,\n" +
                "\t\t\tuserId,\n" +
                "\t\t\tbatchId,\n" +
                "\t\t\tproductType");
        log.info("apiQuerySupplyOrderListPage={}",sql);
        Page page = (Page) params.get("page");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 收卡记录列表查询组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateSupplyCardApiQuerySql(StringBuffer sql, HashMap<String, Object> params) {
        sql.append("select main.area, main.face,sum(main.total)*main.face as totalFace,main.userPrice,main.userId,main.batchId,main.productId,main.orderId,DATE_FORMAT(max(main.startDate),'%Y-%m-%d %H:%i:%S') startDate,main.productType," +
                "sum(main.preCount) preCount,sum(main.dealingCount) dealingCount,sum(main.successCount) successCount," +
                "sum(main.failCount) failCount,sum(main.fixCount) fixCount,sum(main.total) total," +
                "sum(main.settlePrice) settlePrice,sum(main.preJudge) preJudge,sum(main.wrongFace) wrongFace,sum(main.waitSettlement) waitSettlement," +
                "sum(main.disputing) disputing," +
                "sum(waitSlowTrySell) waitSlowTrySell from (" +
                "SELECT  area,userId,batchId,max(startTime) startDate,productType," +
                "case added when 1 then count(1) else 0 end fixCount," +
                "case status when 0 then count(1) when 8 then count(1) else 0 end dealingCount," +
                "case status when 2 then count(1) else 0 end failCount," +
                "case status when 3 then count(1) else 0 end preCount," +
                "case status when 4 then count(1) else 0 end preJudge," +
                "case status when 5 then count(1) else 0 end wrongFace," +
                "case status when 7 then count(1) else 0 end waitSlowTrySell," +
                "case status when 9 then count(1) else 0 end waitSettlement," +
                "case status when 10 then count(1) else 0 end disputing," +
                "case status when 1 then case added when 0 then count(1) else 0 end else 0 end successCount," +
                "count(1) total, face,userPrice,productId,orderId,sum(settlePrice) settlePrice from "
        );
        sql.append(" ( SELECT if(supplyArea = 0, '全国', area) area,userId,batchId,added,status,startTime,productType,userPrice,settlePrice ,face ,productId,orderId,startDate from yuhuang_supply_card  sc ");
        sql.append("where sc.startTime >= #{startTime} AND  sc.startTime <#{endTime}  ");

        SupplyCardViewBean card = (SupplyCardViewBean) params.get("card");
        //会员
        String[] ids = (String[]) params.get("userIds");
        if (!Integer.valueOf(1).equals(card.getInclude()) && ids != null) {
            sql.append(" and sc.userId in (").append(StringUtils.join(ids, ",")).append(") ");
        }
        if (card != null) {
            if (StringUtils.isNotBlank(card.getBatchId())) {
                sql.append(" and sc.batchId = #{card.batchId} ");
            }
            if (StringUtils.isNotBlank(card.getOrderId())) {
                sql.append(" and sc.orderId = #{card.orderId} ");
            }
            if (StringUtils.isNotBlank(card.getProductTypeStr())) {
                sql.append(" and sc.productType IN( ").append(StringUtils.removeStart(card.getProductTypeStr(), ",")).append(")");
            }
            if (card.getFace() != null) {
                sql.append(" and sc.face = #{card.face} ");
            }
            if (card.getLowDiscount() != null) {
                sql.append(" and sc.userPrice>=#{card.lowDiscount} ");
            }
            if (card.getHighDiscount() != null) {
                sql.append(" and sc.userPrice<=#{card.highDiscount} ");
            }
            //销卡地区
            String area = card.getArea();
            if (StringUtils.isNotBlank(area)) {
                if (StringUtils.isNumeric(area)) {
                    sql.append(" and sc.supplyArea = #{card.area} ");
                } else {
                    sql.append(" and sc.supplyArea = 1 and sc.area = #{card.area} ");
                }
            }
        }
        sql.append(")tes GROUP BY  area, userId,batchId,startDate,status,added,productType,face,userPrice ORDER BY startDate desc,userId,batchId,")
                .append("productType) main group by main.userId,main.batchId,main.productType,face,userPrice,area ");
    }

    private void generateSupplyCardApiQueryStatusSql(HashMap<String, Object> params, StringBuffer sql) {
        SupplyCardViewBean card = (SupplyCardViewBean) params.get("card");
        if (Objects.nonNull(card) && Integer.valueOf(1).equals(card.getInclude())
                && StringUtils.isNotBlank(card.getUserAccount())) {
            sql.append(" and u.account<> #{card.userAccount} and i.name<> #{card.userAccount} ");
        }
        String[] strings = Optional.ofNullable(StringUtils.split(card.getStatusArrayStr(), ",")).orElse(new String[]{});
        if (ArrayUtils.isEmpty(strings)) {
            return;
        }
        sql.append(" AND  (");
        int length = strings.length;
        for (int i = 0; i < length; i++) {
            String string = strings[i];
            sql.append(StringUtils.defaultIfBlank(CONDITION.get(string), ""));
            if (i == length - 1) {
                continue;
            }
            sql.append(" OR ");
        }
        sql.append("  )");
    }

    public String putCardFail(HashMap<String, Object> param) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("update yuhuang_supply_card t set t.`status`=2,t.failReason='23005'  ");
        sql.append(" ,t.endTime = DATE_ADD((SELECT max(r.reportTime) FROM yuhuang_supply_report r WHERE r.orderType = 0 ), INTERVAL 10 SECOND)");
        sql.append(" where  t.`status` =#{targetStatus} ");
        String batchIds = (String) param.get("batchIds");
        sql.append(" and t.batchId in (").append(batchIds).append(")");
        return sql.toString();

    }

    /**
     * 按批次查询收卡列表计数
     *
     * @param params 参数
     * @return sql
     */
    public String querySupplyOrderListByBatchCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select count(1) ");

        generateQuerySupplyOrderListByBatchSql(sql, params);
        return sql.toString();
    }

    /**
     * 按批次查询收卡列表
     *
     * @param params 参数
     * @return sql
     */
    public String querySupplyOrderListByBatchPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select sc.id,sc.orderId,sc.userId,sc.cardNumber,sc.productType,sc.area,sc.face,sc.userPrice,sc.settlePrice,sc.status,DATE_FORMAT(sc.startTime,'%Y-%m-%d %H:%i:%S') startTime,")
                .append("DATE_FORMAT(sc.endTime,'%Y-%m-%d %H:%i:%S') endTime,sc.effectTime,sc.added,sc.failReason,sc.judgeFace,co.card_operator_name cardOperatorName ");

        generateQuerySupplyOrderListByBatchSql(sql, params);

        Page page = (Page) params.get("page");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 分页查询销卡订单(批次分组)组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateQuerySupplyOrderListByBatchSql(StringBuffer sql, HashMap<String, Object> params) {
        sql.append(" from yuhuang_supply_card sc,yuhuang_card_operator co where sc.productType=co.card_operator_id and sc.batchId = #{card.batchId} ");
        //查询bean
        SupplyCard card = (SupplyCard) params.get("card");
        if (card.getProductType() != null) {
            sql.append(" and sc.productType = #{card.productType} ");
        }
        if (card.getFace() != null) {
            sql.append(" and sc.face = #{card.face} ");
        }
        if (card.getStatus() != null) {
            if (card.getStatus().equals(50))
            //补结算
            {
                sql.append(" and sc.added = 1 ");
            } else if (card.getStatus().equals(0))
            //在售+慢速试充
            {
                sql.append(" and sc.status in (0,8) ");
            } else {
                sql.append(" and sc.status = #{card.status} ");
            }
        }
    }

}
